<?php

// +---------------------------------------------+
// |     Copyright  2010 - 2018 InterPhoto       |
// |     http://www.weentech.com                 |
// |     This file may not be redistributed.     |
// +---------------------------------------------+

include('includes/Admin.Core.php');

CheckAccess('managedatabase');


PrintHeader('数据库维护');

@set_time_limit(0);

// ######################### Test that the backup directory exists and is writable ##########################

$backupDir = ADMINPATH. 'backup/';
$adminDirname = substr(str_replace(dirname(dirname(__FILE__)), '', dirname(__FILE__)), 1);
$backupUrl = BASEURL . $adminDirname . '/backup/';

$errors = array();

$backupEnabled = false;

if (!is_dir($backupDir))  $errors[] ='数据库备份文件夹 ('.$backupDir.') 不存在!';

@chmod($backupDir,0777);

if (!is_writable($backupDir))	$errors[] = '数据库备份文件夹 ('.$backupDir.') 不可写! - 属性需改为: 0777';

if (!empty($errors)){
	PrintErrors($errors, '初始化错误');
}else{
	$backupEnabled = true;
}

// ####################### FILE READ/WRITE USING GZIP  ######################

function openFileWrite($filename)
{
	if(function_exists('gzopen'))
	{
		$filename .= '.gz';
		$handle = gzopen($filename, "w9");
	}
	else
	{
		$handle = fopen($filename, "w");
	}
	return $handle;
}

function openFileRead($filename)
{
	if(function_exists('gzopen'))
	{
		$handle = gzopen($filename, "r");
	}
	else
	{
		$handle = fopen($filename, "r");
	}
	return $handle;
}

function writeFileData($handle, $data)
{
	if(function_exists('gzwrite'))
	{
		gzwrite($handle, $data);
	}
	else
	{
		fwrite($handle, $data);
	}
}


function readFileData($handle, $size)
{
	if(function_exists('gzread'))
	{
		$data = gzread($handle, $size);
	}
	else
	{
		$data = fread($handle, $size);
	}
	return $data;
}

function eof($handle)
{
	if(function_exists('gzeof'))
	{
		return gzeof($handle);
	}
	else
	{
		return feof($handle);
	}
}

function closeFile($handle)
{
	if(function_exists('gzclose'))
	{
		gzclose($handle);
	}
	else
	{
		fclose($handle);
	}
}



// ####################### END FILE READ FUNCTIONS ######################

function BackupTable($tablename, $fp)
{
	global $DB;

	if(isset($fp))
	{
		// Get the SQL to create the table
		$createTable = $DB->query_first("SHOW CREATE TABLE `$tablename`");

		// Drop if it exists
		$tableDump = "DROP TABLE IF EXISTS `$tablename`;\n" . $createTable['Create Table'] . ";\n\n";

		writeFileData($fp, $tableDump);

		// get data
		$getRows = $DB->query("SELECT * FROM `$tablename`");
		$fieldCount = $DB->get_num_fields();
		$rowCount = 0;

		while ($row = $DB->getrow($getRows))
		{
			$tableDump = "INSERT INTO `$tablename` VALUES(";

			$fieldcounter = -1;
			$firstfield = 1;

			// get each field's data
			while (++$fieldcounter < $fieldCount)
			{
				if (!$firstfield)
				{
					$tableDump .= ', ';
				}else{
					$firstfield = 0;
				}

				if (!isset($row["$fieldcounter"])){
					$tableDump .= 'NULL';
				}else if ($row["$fieldcounter"] != ''){
					$tableDump .= '\'' . addslashes($row["$fieldcounter"]) . '\'';
				}else	{
					$tableDump .= '\'\'';
				}
			}

			$tableDump .= ");\n";

			writeFileData($fp, $tableDump);
			$rowCount++;
		}

		writeFileData($fp, "\n\n\n");

		$msg = "从表 '$tablename' 中备份了 $rowCount 行数据.<br/>";
	}else	{
		$msg = "备份数据库表 '$tablename' 失败!<br/>";
	}

	return $msg;
}


function BackupSingleTable($tablename)
{
	global $backupDir, $backupUrl;

	$theverifycode = substr(md5(rand(0,9999)), 6, 12);

	$filename = $tablename . '_' . $theverifycode . '_' . date("ymd") . '.sql';
	$path = $backupDir . $filename;

	$fp = openFileWrite($path);

	if($fp)
	{
		$msg = BackupTable($tablename, $fp);
		closeFile($fp);
	}

	$msg .= '<br/>数据已备份到文件: ' . $backupUrl . $filename . Iif(function_exists('gzopen'), '.gz'). '<br/>';

	return $msg;
}

function EmptyTable($tablename)
{
	global $DB;
	$DB->query("DELETE FROM `$tablename`");
	$msg .= '<br/>已完成清空数据库表: ' . $tablename . '<br/>';

	return $msg;
}


function BatchBackupTable($tablenames)
{
	global $backupDir, $backupUrl;

	$theverifycode = substr(md5(rand(0,9999)), 6, 12);

	$filename = TABLE_PREFIX . $theverifycode . '_' . date("ymd") . '.sql';

	$path = $backupDir . $filename;

	$fp = openFileWrite($path);

	if($fp)
	{
		for($i = 0; $i < count($tablenames); $i++)
		{
			$msg = $msg . BackupTable($tablenames[$i], $fp);
		}
		closeFile($fp);
	}

	$msg .= '<br/>数据已备份到文件: ' . $backupUrl . $filename . Iif(function_exists('gzopen'), '.gz'). '<br/>';

	return $msg;
}


function ParseQueries($sql, $delimiter)
{
    $matches = array();
    $output = array();

    $queries = explode($delimiter, $sql);
	$sql = "";

	$query_count = count($queries);
	for ($i = 0; $i < $query_count; $i++)
	{
		if (($i != ($query_count - 1)) || (strlen($queries[$i] > 0)))
		{
			$total_quotes = preg_match_all("/'/", $queries[$i], $matches);
			$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $queries[$i], $matches);
			$unescaped_quotes = $total_quotes - $escaped_quotes;

			if (($unescaped_quotes % 2) == 0)
			{
				$output[] = $queries[$i];
				$queries[$i] = "";
			}
			else
			{
				$temp = $queries[$i] . $delimiter;
				$queries[$i] = "";

				$complete_stmt = false;

				for ($j = $i + 1; (!$complete_stmt && ($j < $query_count)); $j++)
				{
					$total_quotes = preg_match_all("/'/", $queries[$j], $matches);
					$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $queries[$j], $matches);
					$unescaped_quotes = $total_quotes - $escaped_quotes;

					if (($unescaped_quotes % 2) == 1)
					{
						$output[] = $temp . $queries[$j];

						$queries[$j] = "";
						$temp = "";

						$complete_stmt = true;
						$i = $j;
					}
					else
					{
						$temp .= $queries[$j] . $delimiter;
						$queries[$j] = "";
					}
				}
			}
		}
	}

	return $output;
}


function RestoreBackup($filename)
{
	global $DB, $backupDir;

	$fp = openFileRead($backupDir . $filename);

	while (!eof($fp))
	{
		$query .= readFileData($fp, 10000);
	}

	closeFile($fp);

	$queries = ParseQueries($query, ';');

	for($i = 0; $i < count($queries); $i++)
	{
		$sql = trim($queries[$i]);

		if(!empty($sql))
		{
			$DB->query($sql);
		}
	}

	PrintRedirect('admin.database.php', 1);
}


function DeleteBackup($filename)
{
	global $backupDir;

	@unlink($backupDir . $filename);

	PrintRedirect('admin.database.php', 1);
}

// ####################### Display Action Results ######################

function PrintResults($title, $message)
{
	PrintSection($title);
	echo '<table width="100%" border="0" cellpadding="5" cellspacing="0">
	<tr>
	<td class="tdrow2" align="left">
	<font class="ohblueb">'.$title .'结果:</font><br/><br/>'. $message . '
	<br/></td>
	</tr>
	</table>';
	EndSection();
}

// ####################### Perform OP on Table ######################

function TableOperation($tablename, $OP)
{
	global $DB;

	$result = $DB->query_first("$OP TABLE `$tablename`");

	return " '" . $tablename . "' : <strong>" . $result['Msg_text'] . "</strong><br/>";
}


function BatchTableOperation($tablenames, $OP)
{
	global $DB;

	$msg = '';

	for($i = 0; $i < count($tablenames); $i++)
	{
		$msg = $msg . TableOperation($tablenames[$i], $OP);
	}

	return $msg;
}

// ######################### Instructions ##########################

function PrintInstructions()
{
	global $adminDirname;
	PrintSection('数据库维护说明');
	echo '<table width="100%" border="0" cellpadding="5" cellspacing="0">
		<tr>
		<td class="tdrow2">
		此工具用于维护InterPhoto图片网站系统的数据库, 操作说明如下:<br />
		<strong>查错</strong> - 检查数据库表是否存在错误.<br />
		<strong>优化</strong> - 回收浪费的空间, 优化数据库表.<br />
		<strong>修复</strong> - 尝试修复数据库表中的错误.<br />
		<strong>清空</strong> - '.TABLE_PREFIX.'sessions表记录和验证用户登陆, '.TABLE_PREFIX.'vvc表记录"验证码". 二者均可清空.<br /><br />
		提示:<br/> 
		1. 全选数据库表可以备份整个数据库, 也可以单独备份某个表;<br/>
		2. 数据备份文件保存在网站的 <b>./'.$adminDirname.'/backup/</b> 目录下;<br/>
		3. 全面备份网站还需要将根目录下的 <b>config/</b> 和 <b>MyWebsiteImages/</b> 目录内的所有文件下传到本地保存.
		</td>
		</tr>
		</table>';
	EndSection();
}

// ####################### List Backup Files #########################

function DisplayBackups()
{
	global $DB, $backupDir, $backupUrl, $adminDirname;

	PrintSection('数据库备份文件');

	echo '<table width="100%" border="0" cellpadding="5" cellspacing="0">
	<tr>
	<td class="tdrow1">文件名 (./'.$adminDirname.'/backup/)</td>
	<td class="tdrow1">大小</td>
	<td class="tdrow1">备份日期</td>
	<td class="tdrow1" colspan="3" align="center">操作</td></tr>';

	if (is_dir($backupDir))
	{
		$dir = opendir($backupDir);

		while (false !== ($file = readdir($dir)))
		{
			if(strpos(strtolower($file),'.sql') > 0)
			{
				$stats = stat($backupDir . $file);

				echo '<tr>
				<td class="tdrow3">' . $file . '</td>
				<td class="tdrow3">' . DisplayFilesize($stats['size']) . '</td>
				<td class="tdrow3">' . DisplayDate($stats['mtime']) . '</td>
				<td class="tdrow3"><a href="admin.database.php?dbaction=restorebackup&filename=' . $file . '" onclick="return confirm(\'确定恢复备份文件到数据库吗?\n\n注: 数据库中对应的原数据将被删除.\');">恢复</a></td>
				<td class="tdrow3"><a href="' . $backupUrl . $file . '">下载</a></td>
				<td class="tdrow3"><a href="admin.database.php?dbaction=deletebackup&filename=' . $file . '" onclick="return confirm(\'确定删除选择的备份文件吗?\');">删除</a></td>
				</tr>';
			}
		}

	}
	echo '</table>';

	EndSection();
}

// ####################### List Database Tables ######################

function DisplayTables()
{
	global $DB, $backupEnabled;

	PrintInstructions();

	echo '<form method="post" action="admin.database.php" name="tables">
	<input type="hidden" name="dbaction" value=""/>';

	PrintSection('数据库列表');

	echo '<table width="100%" border="0" cellpadding="5" cellspacing="0">
	<tr>
	<td class="tdrow1">选择</td>
			<td class="tdrow1">表名称</td>
			<td class="tdrow1">记录数</td>
			<td class="tdrow1">数据大小</td>
			<td class="tdrow1">索引大小</td>
			<td class="tdrow1">空闲</td>
			<td class="tdrow1" colspan="5" align="center">操作</td>
	</tr>';

	$recordsize = $datasize = $indexsize = $freesize = 0;

	$gettables = $DB->query("SHOW TABLE STATUS LIKE '". substr(TABLE_PREFIX, 0, -1) ."\_%'");

	while($tableinfo = $DB->getrow($gettables))
	{

			echo '<tr>
			<td class="tdrow3"><input type="checkbox" name="tablenames[]" value="' . $tableinfo['Name'] . '" checkme="group" /></td>
			<td class="tdrow3">' . $tableinfo['Name'] . '</td>
			<td class="tdrow3">' . $tableinfo['Rows'] . '</td>
			<td class="tdrow3">' . DisplayFilesize($tableinfo['Data_length']) . '</td>
			<td class="tdrow3">' . DisplayFilesize($tableinfo['Index_length']) . '</td>
			<td class="tdrow3">' . Iif($tableinfo['Data_free'] > 0, '<b>', '') . DisplayFilesize($tableinfo['Data_free']) . Iif($tableinfo['Data_free'] > 0, '</b>', '') .  '</td>
			<td class="tdrow3"><a href="admin.database.php?dbaction=checktable&tablename=' . $tableinfo['Name'] . '">查错</a></td>
			<td class="tdrow3"><a href="admin.database.php?dbaction=optimizetable&tablename=' . $tableinfo['Name'] . '">优化</a></td>
			<td class="tdrow3"><a href="admin.database.php?dbaction=repairtable&tablename=' . $tableinfo['Name'] . '">修复</a></td>
			<td class="tdrow3">' . Iif($backupEnabled, '<a href="admin.database.php?dbaction=backuptable&tablename=' . $tableinfo['Name'] . '">备份</a>', '备份') . '</td>';
			
			if($tableinfo['Name'] == TABLE_PREFIX . 'sessions' OR $tableinfo['Name'] == TABLE_PREFIX . 'vvc')
			{
			   echo '<td class="tdrow3"><a href="admin.database.php?dbaction=emptytable&tablename=' . $tableinfo['Name'] . '" onclick="return confirm(\'确定清空此数据库表吗?\');">清空</a></td>';
			}
			else
			{
			   echo '<td class="tdrow3">-</td>';
			}
			
			echo '</tr>';

			$recordsize += $tableinfo['Rows'];
			$datasize += $tableinfo['Data_length'];
			$indexsize += $tableinfo['Index_length'];
			$freesize += $tableinfo['Data_free'];
	}

	echo '<tr>
		<td class="tdrow1"><input type="checkbox" checkall="group" onclick="javascript: return select_deselectAll (\'tables\', this, \'group\');">&nbsp;<font class=ohgreen>全选</font></td>
		<td class="tdrow1">系统共有: '.$DB->get_num_rows() .' 个表</td>
		<td class="tdrow1">'.$recordsize .'</td>
		<td class="tdrow1">'.DisplayFilesize($datasize) .'</td>
		<td class="tdrow1">'.DisplayFilesize($indexsize) .'</td>
		<td class="tdrow1">'.DisplayFilesize($freesize) .'</td>
		<td class="tdrow1"><input type="submit" value="查错" onclick="document.forms[\'tables\'].dbaction.value = \'checkall\';"/></td>
		<td class="tdrow1"><input type="submit" value="优化" onclick="document.forms[\'tables\'].dbaction.value = \'optimizeall\';"/></td>
		<td class="tdrow1"><input type="submit" value="修复" onclick="document.forms[\'tables\'].dbaction.value = \'repairall\';"/></td>
		<td class="tdrow1"><input type="submit" value="备份" onclick="document.forms[\'tables\'].dbaction.value = \'backupall\';" ' . Iif($backupEnabled, '', 'disabled') . '/></td>
		<td class="tdrow1">-</td>
		</tr>
		</table>
		</form>';

	EndSection();

	DisplayBackups();

	PrintFooter();
}

// get the value of $action
$action = ForceIncomingString('dbaction');
$tablename = ForceIncomingString('tablename');
$filename = ForceIncomingString('filename');

switch ($action)
{
	case 'checktable':
		PrintResults('查错数据库表', TableOperation($tablename, 'CHECK'));
		break;
	case 'checkall':
		PrintResults('查错数据库表', BatchTableOperation($_POST['tablenames'], 'CHECK'));
		break;
	case 'optimizetable':
		PrintResults('优化数据库表', TableOperation($tablename, 'OPTIMIZE'));
		break;
	case 'optimizeall':
		PrintResults('优化数据库表',BatchTableOperation($_POST['tablenames'], 'OPTIMIZE'));
		break;
	case 'repairtable':
		PrintResults('修复数据库表',TableOperation($tablename, 'REPAIR'));
		break;
	case 'repairall':
		PrintResults('修复数据库表',BatchTableOperation($_POST['tablenames'], 'REPAIR'));
		break;
	case 'backuptable':
		PrintResults('备份数据库表',BackupSingleTable($tablename));
		break;
	case 'backupall':
		PrintResults('备份数据库表', BatchBackupTable($_POST['tablenames']));
		break;
	case 'emptytable':
		PrintResults('清空数据库表', EmptyTable($tablename));
		break;
	case 'restorebackup':
		RestoreBackup($filename);
		break;
	case 'deletebackup':
		DeleteBackup($filename);
		break;
}

DisplayTables();

?>